<?php

class RelatorioController extends App_Controller_Action
{
    public function indexAction()
    {
    }

    public function tarefasAction()
    {
        $projeto = new Model_Projeto();
        $rowSetProjetos = $projeto->fetchAll();

        $aProjetos = array();
        $aProjetosSelect = array('' => 'Todos');
        $aClientes = array();
        foreach ($rowSetProjetos as $rowProjeto) {
            $rowCliente = $rowProjeto->findParentModel_Cliente();

            $oProjeto = (object) $rowProjeto->toArray();
            $oProjeto->qtTarefas = $rowProjeto->findModel_Tarefa()->count();
            $oProjeto->nomeCliente = $rowCliente->st_nome;

            $aProjetos[$rowProjeto->id_projeto] = $oProjeto;
            $aProjetosSelect[$rowProjeto->id_projeto] = $rowProjeto->st_nome;
            $aClientes[$rowCliente->id_cliente] = $rowCliente->st_nome;
        }
        $this->view->rowSetProjeto   = $aProjetos;
        $this->view->aProjetosSelect = $aProjetosSelect;
        $this->view->aClientes       = $aClientes;

        // filtro
        $filtro = array();
        if ($fk_projeto = $this->_getParam('fk_projeto')) {
            $filtro['t.fk_projeto = ?'] = $fk_projeto;
        }
        $this->view->fk_projeto = $fk_projeto;

        if ($cs_situacao = $this->_getParam('tarefa_cs_situacao')) {
            $filtro['t.cs_situacao = ?'] = $cs_situacao;
        }
        $this->view->tarefa_cs_situacao = $cs_situacao;

        $tarefa = new Model_Tarefa();
        $this->view->rowSetTarefas = $tarefa->getTarefasAgrupadas($filtro);
        $this->view->aTarefaSituacoes = array('' => 'Todas') + $tarefa->getSituacoes();
        $this->_carregarUsuarios();
    }

    public function previsaoAction()
    {
        $modelTarefa = new Model_Tarefa();
        $rowSetTarefa = $modelTarefa->fetchAll('dt_previsao_fim is not null', array('dt_previsao_fim'));
        $this->view->rowSetTarefas = $rowSetTarefa;

        // Projetos
        $projeto = new Model_Projeto();
        $rowSetProjetos = $projeto->fetchAll();

        $aProjetos = array();
        $aProjetosSelect = array('' => 'Todos');
        $aClientes = array();
        foreach ($rowSetProjetos as $rowProjeto) {
            $rowCliente = $rowProjeto->findParentModel_Cliente();

            $oProjeto = (object) $rowProjeto->toArray();
            $oProjeto->qtTarefas = $rowProjeto->findModel_Tarefa()->count();
            $oProjeto->nomeCliente = $rowCliente->st_nome;

            $aProjetos[$rowProjeto->id_projeto] = $oProjeto;
            $aProjetosSelect[$rowProjeto->id_projeto] = $rowProjeto->st_nome;
            $aClientes[$rowCliente->id_cliente] = $rowCliente->st_nome;
        }
        $this->view->rowSetProjeto   = $aProjetos;
        $this->_carregarUsuarios();
        $tarefa = new Model_Tarefa();
        $this->view->aTarefaSituacoes = array('' => 'Todas') + $tarefa->getSituacoes();
    }

    public function tarefasSemanaAction()
    {
        if ($this->_hasParam('disabledLayout')) {
            $this->_helper->layout()->disableLayout();
        }

        $db = Zend_Db_Table::getDefaultAdapter();
        // Tarefas na semana
        $sql = '
        SELECT t.id_tarefa, t.st_nome as tarefa_st_nome, p.st_nome as projeto_st_nome, u.st_nome as usuario_st_nome, t.`dt_previsao_inicio` , t.`dt_previsao_fim`, coalesce(datediff(now(), t.dt_previsao_fim), 0) as dias
        FROM `tarefa` t
        INNER JOIN projeto p ON p.id_projeto = t.fk_projeto
        INNER JOIN usuario u ON u.id_usuario = t.fk_usuario
        WHERE
            WEEK(now()) BETWEEN WEEK(t.dt_previsao_inicio) AND WEEK(t.dt_previsao_fim)
            OR
            WEEK(now()) BETWEEN WEEK(t.dt_inicio) AND WEEK(t.dt_previsao_fim)
        ORDER BY t.id_tarefa, p.st_nome, t.st_nome';
        $this->view->rowSetTarefasSemana = $db->query($sql)->fetchAll();

        $sql = '
        SELECT t.id_tarefa, t.st_nome as tarefa_st_nome, p.st_nome as projeto_st_nome, u.st_nome as usuario_st_nome, coalesce(datediff(now(), t.dt_previsao_fim), 0) as dias
        FROM `tarefa` t
        INNER JOIN projeto p ON p.id_projeto = t.fk_projeto
        INNER JOIN usuario u ON u.id_usuario = t.fk_usuario
        WHERE
            WEEK(t.dt_previsao_fim) < WEEK(now())
            AND (t.dt_fim is null OR t.dt_inicio is null)
        ORDER BY t.id_tarefa, p.st_nome, t.st_nome';
        $this->view->rowSetTarefasAtrasadas = $db->query($sql)->fetchAll();
    }

    public function geralAction()
    {
        $db = Zend_Db_Table::getDefaultAdapter();
        // horas por mês
        $sql = 'select id_usuario, st_nome,
                TIMEDIFF(st_horas, TIME_FORMAT(concat(u.qt_horas_mes, ":00"), "%H:%m")) as qt_diff_horas,
                tb.* from usuario u
                inner join (
                    select
                                DATE_FORMAT(dt_cadastro, "%Y%m") as ref,
                                fk_usuario,
                                SEC_TO_TIME(sum(TIME_TO_SEC(qt_horas))) as st_horas
                                from tarefa_atividade
                                where dt_cadastro >= DATE_FORMAT("2012-11-01", "%Y-%m-%d")
                                group by ref, fk_usuario
                        having st_horas > 0
                ) as tb on u.id_usuario = tb.fk_usuario
                order by ref desc, u.st_nome';
        $this->view->aHorasPorMes = $db->query($sql)->fetchAll();

        // Tarefas abertas X fechadas
        $sql = 'select ref, sum(abertos) as abertas, sum(fechados) as fechadas, tarefas_ativas_por_mes(ref) as ativas
                from (
                    select * from (
                    select DATE_FORMAT(dt_cadastro, "%Y%m") as ref, count(0) as abertos, 0 as fechados
                    from tarefa group by ref order by ref desc ) tb1
                UNION
                    select * from (
                    select DATE_FORMAT(dt_fim, "%Y%m") as ref, 0 as abertos, count(0) as fechados
                    from tarefa group by ref order by ref desc ) tb2
                    ) tb3
                GROUP by ref
                ORDER by ref desc';
        $this->view->aTarefasAbertasFechadas = $db->query($sql)->fetchAll();

        // Projetos abertos X fechados
        $sql = 'select ref, sum(abertos) as abertos, sum(fechados) as fechados, projetos_ativas_por_mes(ref) as ativos
                from (
                    select * from (
                        select DATE_FORMAT(dt_inicio, "%Y%m") as ref, count(0) as abertos, 0 as fechados
                        from projeto group by ref order by ref desc ) tb1
                    UNION
                    select * from (
                        select DATE_FORMAT(dt_fim, "%Y%m") as ref, 0 as abertos, count(0) as fechados
                        from projeto group by ref order by ref desc ) tb2
                    ) tb3
                GROUP by ref
                ORDER by ref desc';
        $this->view->aProjetosAbertosFechados = $db->query($sql)->fetchAll();
    }

    public function projetosPorMesAction()
    {
        $stRef = $this->_getParam('st_mes');
        $tipo = $this->_getParam('tipo');

        //Ativos
        switch ($tipo) {
            case 'Abertos' :
                $campo = 'dt_inicio';
                break;
            case 'Fechados' :
                $campo = 'dt_fim';
                break;
            case 'Ativos' :
                $sql = 'select
                       *
                    from projeto
                    where
                       ' . $stRef .' BETWEEN COALESCE(DATE_FORMAT(dt_inicio, "%Y%m"), "000000") AND COALESCE(DATE_FORMAT(dt_fim, "%Y%m"), 210001);';
                break;
        }


        if (empty($sql)) {
            $sql = 'select * from projeto
                    where DATE_FORMAT(%s, "%s") = "' . $stRef .'" order by st_nome';
            $sql = sprintf($sql, $campo, '%Y%m');

            if (null == $stRef) {
                $sql = 'select * from projeto
                        where ' . $campo .' is null order by st_nome';
            }
        }

        $db = Zend_Db_Table::getDefaultAdapter();
        $this->view->aProjetos = $db->query($sql)->fetchAll();
        $this->view->tipo  = $tipo;
        $this->view->stRef = $stRef;
    }

    public function tarefasPorUsuarioAction()
    {
        $id_usuario = $this->_getParam('id_usuario');
        $ref = $this->_getParam('ref');

        $db = Zend_Db_Table::getDefaultAdapter();
        // horas por mês
        $sql = 'select id_usuario, st_nome, tb.* from usuario u
                inner join (
                    select
                                DATE_FORMAT(dt_cadastro, "%Y%m") as ref,
                                fk_usuario,
                                SEC_TO_TIME(sum(TIME_TO_SEC(qt_horas))) as st_horas
                                from tarefa_atividade
                                WHERE fk_usuario = ' . $id_usuario . ' AND DATE_FORMAT(dt_cadastro, "%Y%m") = "' .$ref . '"
                                group by ref, fk_usuario
                        having st_horas > 0
                ) as tb on u.id_usuario = tb.fk_usuario
                order by ref desc, u.st_nome';
        $this->view->aHorasPorMes = $db->query($sql)->fetchAll();

        // Horas por dia
        $sql = 'select
                    DATE_FORMAT(dt_cadastro, "%Y%m%d") as ref,
                    SEC_TO_TIME(sum(TIME_TO_SEC(qt_horas))) as st_horas
                    from tarefa_atividade
                    WHERE fk_usuario = ' . $id_usuario . ' AND DATE_FORMAT(dt_cadastro, "%Y%m") = "' .$ref . '"
                    group by ref
                    order by ref desc';
        $this->view->aHorasPorDia = $db->query($sql)->fetchAll();

        // Horas por projeto
        $sql = 'select id_projeto, p.st_nome, count(fk_tarefa) as qt_tarefas, SEC_TO_TIME(sum(TIME_TO_SEC(st_horas))) as qt_horas
                from projeto p
                inner join (
                  select fk_projeto, tb.* from tarefa t
                                inner join (
                                    select
                                                fk_tarefa,
                                                SEC_TO_TIME(sum(TIME_TO_SEC(qt_horas))) as st_horas
                                                from tarefa_atividade
                                                WHERE fk_usuario = ' . $id_usuario . ' AND DATE_FORMAT(dt_cadastro, "%Y%m") = "' .$ref . '"
                                                group by fk_tarefa
                                        having st_horas > 0
                                ) as tb on t.id_tarefa = tb.fk_tarefa
                  ) as tb_tarefa on tb_tarefa.fk_projeto = p.id_projeto
                group by id_projeto
                order by qt_horas desc';
        $this->view->aHorasPorProjeto = $db->query($sql)->fetchAll();

        // Grafico
        $sql = 'select id_projeto, p.st_nome, SEC_TO_TIME(sum(st_horas)) as qt_horas
                , round((sum(st_horas) /

                (select sum(TIME_TO_SEC(qt_horas))
                                from tarefa_atividade
                         WHERE fk_usuario = ' . $id_usuario . ' AND DATE_FORMAT(dt_cadastro, "%Y%m") = "' .$ref . '")
                ) * 100, 2)
                as qt_horas_percentual

                from projeto p inner join
                      ( select fk_projeto, tb.* from tarefa t inner join
                             ( select fk_tarefa, sum(TIME_TO_SEC(qt_horas)) as st_horas
                                from tarefa_atividade
                         WHERE fk_usuario = ' . $id_usuario . ' AND DATE_FORMAT(dt_cadastro, "%Y%m") = "' .$ref . '"
                         group by fk_tarefa having st_horas > 0
                      ) as tb on t.id_tarefa = tb.fk_tarefa ) as tb_tarefa on tb_tarefa.fk_projeto = p.id_projeto
                group by id_projeto order by qt_horas desc';
        $this->view->aGrafico = $db->query($sql)->fetchAll();

        // Atividades
        $sql = 'select * from tarefa_atividade
                WHERE fk_usuario = ' . $id_usuario . ' AND DATE_FORMAT(dt_cadastro, "%Y%m") = "' .$ref . '"
                order by dt_cadastro desc';
        $this->view->aAtividades = $db->query($sql)->fetchAll();
    }

    public function mediaHorasGuiaAction()
    {
        $sql = "select qt_horas, qtTarefas, nr_media_dias, st_nome
                from tarefa_guia tg
                inner join (
                    select fk_tarefa_guia, count(fk_tarefa_guia) as qtTarefas ,
                    SEC_TO_TIME(avg(TIME_TO_SEC(qt_horas))) as qt_horas
                  from tarefa t
                  inner join
                     (select
                            fk_tarefa, SEC_TO_TIME(sum(TIME_TO_SEC(qt_horas))) as qt_horas
                      from tarefa_atividade where qt_horas > time('2012-12-12 00:20:00') group by fk_tarefa) tb_horas on tb_horas.fk_tarefa = t.id_tarefa
                group by fk_tarefa_guia ) tb2 on tb2.fk_tarefa_guia = tg.id_tarefa_guia";
        $db = Zend_Db_Table::getDefaultAdapter();
        $this->view->aTarefas = $db->query($sql)->fetchAll();
    }

    public function atividadesDiariasAction()
    {
        $id_usuario = $this->_getParam('id_usuario', 1);
        $ref = $this->_getParam('ref');
        $sData = $this->_getParam('sData', date('d/m/Y'));
        $this->view->id_usuario = $id_usuario;

        $db = Zend_Db_Table::getDefaultAdapter();

        $modelUsuario   = new Model_Usuario();
        $rowUsuario     = $modelUsuario->find($id_usuario)->current();
        $rowSetProjetos = $rowUsuario->findModel_ProjetoViaModel_ProjetoUsuario();

        // Atividades
        $sql = 'select * from tarefa_atividade
                WHERE fk_usuario = ' . $id_usuario . ' AND qt_horas > 0 -- AND DATE_FORMAT(dt_cadastro, "%Y%m%d") = "' . $sData . '"
                order by dt_cadastro';
        $sql = 'select t.fk_projeto, p.st_nome as projeto_st_nome, t.st_nome as tarefa_st_nome, ta.fk_tarefa, ta.qt_horas, st_atividade
                from tarefa_atividade ta
                inner join tarefa t on ta.fk_tarefa = t.id_tarefa
                inner join projeto p on t.fk_projeto = p.id_projeto
                WHERE
                    ta.fk_usuario = ' . $id_usuario . '  and ta.qt_horas > 0
                    AND DATE_FORMAT(ta.dt_cadastro, "%d/%m/%Y") = "' . $sData . '"
                order by p.id_projeto, ta.dt_cadastro';
        $this->view->aAtividades = $db->query($sql)->fetchAll();
        $this->_carregarUsuarios();
        $this->view->sData = $sData;
        $this->view->bPDF = false;

        if ($this->_getParam('gerar-pdf')) {
            $this->view->bPDF = true;
            $html = $this->view->render('relatorio/atividades-diarias.phtml');
            $nomeArquivo = $id_usuario . $sData . '.pdf';
            $pdf = new App_Pdf();
            $pdf->preparar('RH', 'Gestão de Atividades', '', 'alfa');
            $pdf->exibirLogo = false;
            $pdf->AddPage();
            $pdf->SetFont('helvetica', '', 8);
            $pdf->writeHTML($html, true, false, false, false, '');
            $pdf->Output($nomeArquivo, 'I'); die;
        }
    }
}
